package mapi.flightrecorder;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Date;
import java.util.TimeZone;

import android.R.string;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.util.Log;

public class DataBaseHelper extends SQLiteOpenHelper {

	private static String DB_PATH = "/data/data/mapi.flightrecorder/databases/";
	private static String DB_NAME = "db";
	private SQLiteDatabase myDataBase;
	private final Context myContext;

	public DataBaseHelper(Context context) {
		super(context, DB_NAME, null, 200);
		this.myContext = context;
	}

	public void createDataBase() throws IOException {
		try {
			SQLiteDatabase db = SQLiteDatabase.openDatabase(DB_PATH + DB_NAME,
					null, 0);
			db.close();
			Log.d("DatabaseHelper", "The base exist");
		} catch (Exception e) {
			Log.d("DatabaseHelper", "The doesn't exist");
			this.getReadableDatabase(); // create the base if not there
			copyDataBase();
		}
	}

	/**
	 * Copies your database from your local assets-folder to the just created
	 * empty database in the system folder, from where it can be accessed and
	 * handled. This is done by transfering bytestream.
	 * */
	private void copyDataBase() throws IOException {

		// Open your local db as the input stream
		InputStream myInput = myContext.getAssets().open(
				"database/initialdb.db");

		// Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;

		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		// transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}

		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

	}

	public void openDataBase() throws SQLException {

		// Open the database
		String myPath = DB_PATH + DB_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READONLY);

	}

	@Override
	public synchronized void close() {

		if (myDataBase != null)
			myDataBase.close();

		super.close();

	}

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	public long AddLocation(long timestamp, double latitude, double longitude,
			double altitude, float speed, float accuracy, float bearing,
			double sessionid) {
		SQLiteDatabase db = this.getWritableDatabase();
		
		Cursor cursor = db.query("airports", new String[] { "(abs(latitude - "
				+ latitude + ") + abs(longitude - " + longitude + ")) dist", "icao" },
				null, null, null,
				null, "dist", "4");
		
		cursor.moveToPosition(0);
		String airport1 = cursor.getString(1);
		
		cursor.moveToPosition(1);
		String airport2 = cursor.getString(1);
		
		cursor.moveToPosition(2);
		String airport3 = cursor.getString(1);
		
		cursor.moveToPosition(3);
		String airport4 = cursor.getString(1);
		cursor.close();
		
		ContentValues contentvalues = new ContentValues();
		contentvalues.put("timestamp", timestamp);
		contentvalues.put("latitude", latitude);
		contentvalues.put("longitude", longitude);
		contentvalues.put("altitude", altitude);
		contentvalues.put("speed", speed);
		contentvalues.put("accuracy", accuracy);
		contentvalues.put("bearing", bearing);
		contentvalues.put("sessionid", sessionid);
		contentvalues.put("airport1", airport1);
		contentvalues.put("airport2", airport2);
		contentvalues.put("airport3", airport3);
		contentvalues.put("airport4", airport4);
		
				

		long rowid = db.insert("locations", null, contentvalues);
		db.close();
		return rowid;
	}

	public long Count() {
		SQLiteDatabase db = this.getWritableDatabase();
		long count = DatabaseUtils.queryNumEntries(db, "locations");
		db.close();
		return count;
	}

	public Cursor GetCursorListviewExport() {
		// select datetime(substr(max(timestamp),0,11),'unixepoch') stop from
		// locations group by sessionid;
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db
				.query("locations",
						new String[] {
								"sessionid _id",
								"datetime(min(timestamp) / 1000,'unixepoch') start",
								"datetime(max(timestamp) / 1000,'unixepoch') stop",
								"sessionid",
								"count(*) count",
								"time((max(timestamp) - min(timestamp)) / 1000,'unixepoch') last",
								"avg(accuracy) avgaccuracy" }, null, null,
						"sessionid", null, "sessionid" + " DESC");

		return cursor;
	};

	public String GetLast(double sessionid) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db
				.query("locations",
						new String[] { "time((max(timestamp) - min(timestamp)) / 1000,'unixepoch') last" },
						"sessionid=" + sessionid, null, "sessionid", null, null);

		cursor.moveToFirst();
		String last = cursor.getString(0);

		return last;
	}

	public int GetUpdatesCount(double sessionid) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.query("locations",
				new String[] { "count(*) count" }, "sessionid=" + sessionid,
				null, "sessionid", null, null);

		cursor.moveToFirst();
		int UpdatesCount = cursor.getInt(0);
		return UpdatesCount;
	}

	public String GetAvgAccuracy(double sessionid) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.query("locations", new String[] { "avg(accuracy)" },
				"sessionid=" + sessionid, null, "sessionid", null, null);

		cursor.moveToFirst();
		String AvgAccuracy = cursor.getString(0);
		return AvgAccuracy;
	}

	public String GetStart(double sessionid) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db
				.query("locations",
						new String[] { "datetime(min(timestamp) / 1000,'unixepoch') start" },
						"sessionid=" + sessionid, null, "sessionid", null, null);

		cursor.moveToFirst();
		String Start = cursor.getString(0);
		return Start;
	}

	public String GetEmailBody(int sessionid) {
		// select datetime(substr(max(timestamp),0,11),'unixepoch') stop from
		// locations group by sessionid;
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db
				.query("locations",
						new String[] {
								"datetime(min(timestamp) / 1000,'unixepoch') start",
								"datetime(max(timestamp) / 1000,'unixepoch') stop",
								"time((max(timestamp) - min(timestamp)) / 1000,'unixepoch') last",
								"count(*) count", "avg(accuracy) avgaccuracy" },
						"sessionid=" + sessionid, null, "sessionid", null,
						"sessionid DESC");
		cursor.moveToFirst();
		String emailbody = "Session ID : " + sessionid + "\n";
		emailbody += "Start : " + cursor.getString(0) + "\n";
		emailbody += "Stop : " + cursor.getString(1) + "\n";
		emailbody += "Last : " + cursor.getString(2) + "\n";
		emailbody += "Updates : " + cursor.getString(3) + "\n";
		emailbody += "Average accuracy : " + cursor.getString(4) + "\n";

		return emailbody;
	};

	public double NewSession(String name) {
		String myPath = DB_PATH + DB_NAME;
		SQLiteDatabase db = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READWRITE);

		ContentValues contentvalues = new ContentValues();
		contentvalues.put("name", name);
		long sessionid = db.insert("sessions", null, contentvalues);
		db.close();
		return sessionid;
	}

	public Cursor GetCursorSessionInfos(double sessionid) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.query("locations", new String[] {
				"time((max(timestamp) - min(timestamp)) / 1000,'unixepoch')" // "yyyy-MM-dd'T'HH:mm:ss'Z'
				,
				"strftime('%Y-%m-%d %H:%M',min(timestamp) / 1000,'unixepoch')",
				"strftime('%Y-%m-%d %H:%M',max(timestamp) / 1000,'unixepoch')",
				"avg(accuracy)", "count(*)" }, "sessionid=" + sessionid, null,
				"sessionid", null, null);

		return cursor;
	};

	public Cursor GetCursorSession(int sessionid) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.query("locations", new String[] { "timestamp",
				"longitude", "latitude", "altitude", "bearing","speed","accuracy","airport1","airport2","airport3","airport4" }, "sessionid="
				+ sessionid, null, null, null, "timestamp");

		return cursor;
	};

	public String GetFilename(int sessionid) {
		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db
				.query("locations",
						new String[] { "strftime('%Y-%m-%d_%Hh%M',min(timestamp) / 1000,'unixepoch')" },
						"sessionid = " + sessionid, null, "sessionid", null,
						null);
		cursor.moveToFirst();
		String filename = cursor.getString(0);
		cursor.close();
		db.close();
		return filename;
	}
	public Location GetAirportLocation(String icao){
		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db
				.query("airports",
						new String[] { "latitude", "longitude", "altitude" },
						"icao =\"" + icao + "\"" , null, null, null,
						null);
		cursor.moveToFirst();
		Location airportlocation = new Location("provider");
		airportlocation.setLatitude(cursor.getDouble(0));
		airportlocation.setLongitude(cursor.getDouble(1));
		airportlocation.setAltitude(cursor.getDouble(2));
		cursor.close();
		db.close();
		return airportlocation;
	}

	public Cursor GetClorAirport(int sessionid, double longitude,
			double latitude) {
		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db
				.query("locations",
						new String[] { "strftime('%Y-%m-%d_%Hh%M',min(timestamp) / 1000,'unixepoch')" },
						"sessionid = " + sessionid, null, "sessionid", null,
						null);
		cursor.moveToFirst();

		// TODO Auto-generated method stub
		return null;
	}
	
	double AltCloeAirport(double longitude, double latitude) {
		SQLiteDatabase db = this.getWritableDatabase();
		double distance = 0.3;
		

		Cursor cursor = db.query("airports", new String[] { "(abs(latitude - "
				+ latitude + ") + abs(longitude - " + longitude + ")) dist", "altitude" },
				" dist<" + distance, null, null,
				null, null);
		boolean CursorEmpty = cursor.moveToFirst();
		double altitude = cursor.getDouble(1);
		altitude = altitude * 3.280839895;		
		cursor.close();
		db.close();

		if (CursorEmpty == true) {
			return 9999;
		} else {
			return altitude;
		}
	}

	boolean IsBelowCloseAirrt(double latitude, double longitude,
			double altitude) {
		SQLiteDatabase db = this.getWritableDatabase();
		double distance = 0.1;
		altitude = altitude * 3.280839895; // conversion en feet
		// sqlite> select icao, (abs(42.47 - latitude) + abs(-71.289 -
		// longitude)) dist from airports where dist<0.2;
		// 42.47|-71.289|133

		Cursor cursor = db.query("airports", new String[] { "(abs(latitude - "
				+ latitude + ") + abs(longitude - " + longitude + ")) dist" },
				"altitude > " + altitude + " AND dist<" + distance, null, null,
				null, null);
		boolean CursorEmpty = cursor.moveToFirst();
		cursor.close();
		db.close();

		if (CursorEmpty == true) {
			return true;
		} else {
			return false;
		}
	}

}// MongpsloggerDatabase class

// sqlite3 /data/data/marc.gpslogger/databases/database

